package com.hsnn.medstgmini.base.std.dao.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.std.dao.CompDrugProfileDao;
import com.hsnn.medstgmini.base.std.dao.StdCompdrugprofileDao;
import com.hsnn.medstgmini.base.std.model.StdCompprofile;
import com.hsnn.medstgmini.base.std.model.form.CompDrugProfileForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 企业产品资质管理
 * @author 蔡春龙
 * @date 2015年9月14日
 */
@Repository
public class CompDrugProfileDaoImpl implements CompDrugProfileDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	@Autowired
	private HibernateSqlUtil sqlUtil;
	
	@Autowired
	private StdCompdrugprofileDao stdCompdrugprofileDao;

	/**
	 * @category 查看企业产品资质列表
	 * @date 2015年9月14日
	 * @param page
	 * @return
	 */
	public Pagination getCompProfileList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks remark, b.first_level_indi_name firstLevelIndiName, a.audit_status auditStatus, DATE_FORMAT( a.audit_datetime, '%Y-%m-%d' ) auditDatetime, a.audit_remark auditRemark, c.name auditUser FROM std_compdrugprofile a LEFT JOIN dic_entprod_profile b ON a.profile_type = b.dic_entprod_profile_id LEFT JOIN sys_user c ON a.audit_user = c.id WHERE 1 = 1 AND a.comp_id = :compId AND a.comp_prod_id = :prodId ");

		// 查询条件
		Map<String, Object> params = new HashMap<String, Object>();
		CompDrugProfileForm form = (CompDrugProfileForm) page.getForm();
		params.put("compId", form.getCompId());
		params.put("prodId", form.getProdId());

		if (form.getAuditStatus() != null) {
			sb.append(" AND a.audit_status = :auditStatus ");
			params.put("auditStatus", form.getAuditStatus());
		}

		if (StringUtils.isNotBlank(form.getProfileCode())) {
			sb.append(" AND a.profile_code = :profileCode ");
			params.put("profileCode", form.getProfileCode());
		}

		if (StringUtils.isNotBlank(form.getProfileName())) {
			sb.append(" AND a.profile_name LIKE :profileName ");
			params.put("profileName", "%" + form.getProfileName() + "%");
		}

		sb.append(" ORDER BY a.compprofile_id ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}

	/**
	 * @category 根据id查询药品id
	 * @date 2015年9月14日
	 * @param drugId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getDrugInfoById(String prodId) {
		String sql = "SELECT a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form_name formName, b.spec_name specName, a.comp_name compName FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE a.prod_id = :prodId";
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("prodId", prodId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return (Map<String, Object>) query.uniqueResult();
	}

	/**
	 * @category 查询药品资质类型列表
	 * @date 2015年9月15日
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> getDrugProfileTypeList() {
		String sql = "SELECT a.dic_entprod_profile_id id, a.first_level_indi_name name FROM dic_entprod_profile a";
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 保存药品资质
	 * @date 2015年9月15日
	 * @param form
	 */
	public void saveSelfDrugProfile(CompDrugProfileForm form) {
		String compProfileId = form.getCompProfileId();
		if (StringUtils.isBlank(compProfileId)) { // 新增
			compProfileId = UUID.randomUUID().toString();
			form.setCompProfileId(compProfileId);
			insertDrugProfile(form);
		} else { // 编辑
			updateDrugProfile(form);
		}
		// 记录日志
		writeDrugProfileLog(compProfileId);
	}

	private void insertDrugProfile(CompDrugProfileForm form) {
		Session session = factory.getCurrentSession();
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO std_compdrugprofile VALUES ");
		sb.append("(:compProfileId, :compId, :prodId, :profileCode, :profileName, :profileType, :profileStarttime, :profileEndtime, :profilePicAddr, :profilePicShortcutAddr, 0, :remark, :updUser, sysdate(), :auditStatus, null, null, null)");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setString("compProfileId", form.getCompProfileId());
		query.setString("compId", form.getCompId());
		query.setString("prodId", form.getProdId());
		query.setString("profileCode", "ZZ" + new Date().getTime());
		query.setString("profileName", form.getProfileName());
		query.setString("profileType", form.getProfileType());
		query.setString("profileStarttime", form.getProfileStarttime());
		query.setString("profileEndtime", form.getProfileEndtime());
		query.setString("profilePicAddr", form.getProfilePicAddr());
		query.setString("profilePicShortcutAddr",
				form.getProfilePicShortcutAddr());
		query.setString("remark", form.getRemark());
		query.setInteger("auditStatus", form.getAuditStatus());
		query.setString("updUser", form.getUpdUser());
		query.executeUpdate();
		session.flush();
	}

	private void updateDrugProfile(CompDrugProfileForm form) {
		Session session = factory.getCurrentSession();
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE std_compdrugprofile SET upd_user = :updUser, upd_datetime = sysdate(), comp_id = :compId, ");
		sb.append("profile_name = :profileName, profile_type = :profileType, profile_starttime = :profileStarttime, ");
		sb.append("profile_endtime = :profileEndtime, ");
		String profilePicAddr = form.getProfilePicAddr();
		if (StringUtils.isNotBlank(profilePicAddr)) {
			sb.append(" profile_pic_addr = :profilePicAddr, ");
		}
		String profilePicShortcutAddr = form.getProfilePicShortcutAddr();
		if (StringUtils.isNotBlank(profilePicShortcutAddr)) {
			sb.append(" profile_pic_shortcut_addr = :profilePicShortcutAddr, ");
		}
		sb.append("remarks = :remark WHERE compprofile_id = :compProfileId ");

		SQLQuery query = session.createSQLQuery(sb.toString());

		query.setString("compProfileId", form.getCompProfileId());
		query.setString("compId", form.getCompId());
		query.setString("profileName", form.getProfileName());
		query.setString("profileType", form.getProfileType());
		query.setString("profileStarttime", form.getProfileStarttime());
		query.setString("profileEndtime", form.getProfileEndtime());
		query.setString("remark", form.getRemark());
		query.setString("updUser", form.getUpdUser());
		if (StringUtils.isNotBlank(profilePicAddr)) {
			query.setString("profilePicAddr", form.getProfilePicAddr());
		}
		if (StringUtils.isNotBlank(profilePicShortcutAddr)) {
			query.setString("profilePicShortcutAddr",
					form.getProfilePicShortcutAddr());
		}
		query.executeUpdate();
		session.flush();
	}

	/**
	 * @category 记录药品资质日志
	 * @date 2015年9月15日
	 * @param ids
	 */
	private void writeDrugProfileLog(String... ids) {
		
		
		Session session =factory.getCurrentSession();
		// ----begin	
        StringBuffer detail = new StringBuffer();
		StringBuffer sb = new StringBuffer()
		.append("  select profile_code profileCode, profile_name profileName,a.first_level_indi_name  profileType,profile_pic_addr profilePicAddr,profile_pic_shortcut_addr  profilePicShortcutAddr,remarks remarks,b.status  STATUS  ")
		.append(" from std_compdrugprofile b ")
		.append(" LEFT JOIN dic_entprod_profile a ON a.dic_entprod_profile_id=b.profile_type ")
		.append("  where compprofile_id =  :compprofileId  ")
		.append(" union all ")
		.append(" select t.* from (select  profile_code profileCode, profile_name profileName, a.first_level_indi_name  profileType, profile_pic_addr profilePicAddr, profile_pic_shortcut_addr  profilePicShortcutAddr, remarks remarks, b.status  STATUS ")
		.append("  from std_compdrugprofile_log b LEFT JOIN dic_entprod_profile a ON a.dic_entprod_profile_id=b.profile_type where compprofile_id = :compprofileId  order by b.upd_datetime desc limit 1 ) t");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compprofileId",ids[0]);
		List<?> list = sqlUtil.getSqlList(sb.toString(), params);
		
		String[] colunm ={"资质编号","资质名称","资质类型","资质缩略图","备注","","状态"};
		if(list.size()==1){
			Object[] obj =(Object[]) list.get(0);
			obj[6] = obj[6]==null?"":"1".equals(obj[6].toString())?"启用":"停用";
			for(int i = 0;i<obj.length;i++){
				detail.append(";").append(colunm[i]).append(":").append(obj[i]==null?"":obj[i].toString());
			}
			detail.delete(0, 1);
		}else{
			Object[] obj1 =(Object[]) list.get(0);//修改后
			Object[] obj2 =(Object[]) list.get(1);//修改前
			obj1[6] = obj1[6]==null?"":"1".equals(obj1[6].toString())?"启用":"停用";
			obj2[6] = obj2[6]==null?"":"1".equals(obj2[6].toString())?"启用":"停用";
			for(int i = 0;i<obj1.length;i++){
				if(!(obj1[i]==null?"":obj1[i].toString()).equals(obj2[i]==null?"":obj2[i].toString())){
					detail.append(";").append(colunm[i]).append(":").append(obj2[i]==null?"":obj2[i].toString()).append(",").append(obj1[i]==null?"":obj1[i].toString());
				}
				
			}
			detail.delete(0, 1);
		}
		// ----end
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into std_compdrugprofile_log(log_id,compprofile_id,comp_id,comp_prod_id,profile_code,profile_name,profile_type,profile_starttime,profile_endtime,profile_pic_addr,profile_pic_shortcut_addr,status, ")
			.append(" remarks,upd_datetime,upd_user,audit_status,audit_datetime,audit_remark,audit_user,detail ")
			.append(" ) select ")
		    .append(" UUID(),t.compprofile_id,t.comp_id,t.comp_prod_id,t.profile_code,t.profile_name,t.profile_type,t.profile_starttime,t.profile_endtime,t.profile_pic_addr,t.profile_pic_shortcut_addr,t.status,t.remarks,t.upd_datetime,t.upd_user, ")
		    .append(" t.audit_status,t.audit_datetime,t.audit_remark,t.audit_user,:detail from std_compdrugprofile t where t.compprofile_id= :compprofileId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("compprofileId",ids[0]);
		query.setString("detail",detail.toString());
		query.executeUpdate();
		session.flush();
		
		/*String sql = "INSERT INTO std_compdrugprofile_log SELECT UUID(), a.* FROM std_compdrugprofile a WHERE a.compprofile_id IN (:ids)";
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setParameterList("ids", ids);
		query.executeUpdate();
		session.flush();*/
	}

	/**
	 * @category 提交药品资质
	 * @date 2015年9月15日
	 * @param form
	 */
	public void submitCompProfile(CompDrugProfileForm form) {
		String sql = "UPDATE std_compdrugprofile SET upd_user = :updUser, upd_datetime = sysdate(), audit_status = 0 WHERE compprofile_id = :compProfileId";
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("compProfileId", form.getCompProfileId());
		query.setString("updUser", form.getUpdUser());
		query.executeUpdate();
		session.flush();

		// 记录日志
		writeDrugProfileLog(form.getCompProfileId());
	}

	/**
	 * @category 变更启用停用状态
	 * @date 2015年9月15日
	 * @param form
	 */
	public void changeStatus(CompDrugProfileForm form) {
		String sql = "UPDATE std_compdrugprofile SET upd_user = :updUser, upd_datetime = sysdate(), status = :status WHERE compprofile_id = :compProfileId";
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("compProfileId", form.getCompProfileId());
		query.setInteger("status", form.getStatus());
		query.setString("updUser", form.getUpdUser());
		query.executeUpdate();
		session.flush();

		// 记录日志
		writeDrugProfileLog(form.getCompProfileId());
	}

	/**
	 * @category 查询有资质需要审核的药品列表
	 * @date 2015年9月15日
	 * @param page
	 * @return
	 */
	public Pagination getAuditCompDrugList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form_name formName, b.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, a.prod_comp_id prodCompId, a.comp_name prodCompName, a.status FROM std_proddruginfo a LEFT JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE a.prod_id IN ( SELECT e.comp_prod_id FROM std_compdrugprofile e WHERE e.audit_status = 0 ) ");

		// 查询条件
		CompDrugProfileForm form = (CompDrugProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode.trim());
			}

			// 通用名
			String drugName = form.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				sb.append(" AND (b.qualityspec_name LIKE :drugName OR b.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + drugName.trim() + "%");
			}

			// 企业id
			String prodId = form.getProdId();
			if (StringUtils.isNotBlank(prodId)) {
				sb.append(" AND a.prod_comp_id = :prodId ");
				params.put("prodId", prodId);
			}

			// 生产企业名称
			String compName = form.getCompName();
			if (StringUtils.isNotBlank(compName)) {
				sb.append(" AND a.comp_name LIKE :compName ");
				params.put("compName", "%" + compName.trim() + "%");
			}
			//状态
			String status = null;
			if (form.getStatus() != null ) {
				 status = form.getStatus()+"";
			}
			if (StringUtils.isNotBlank(status)) {
				sb.append(" AND a.status = :status ");
				params.put("status", status);
			}
		}
		sb.append(" order by a.drug_code desc ");
		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 审核药品资质
	 * @date 2015年9月9日
	 * @param compProfile
	 * @param user
	 */
	public void auditCompProfile(StdCompprofile compProfile, SysUser user) {
		Session session = factory.getCurrentSession();
		String sql = "UPDATE std_compdrugprofile SET status = :status, audit_status = :auditStatus, audit_remark = :auditRemark, audit_datetime = SYSDATE(), audit_user = :updUser, upd_user = :updUser, upd_datetime = SYSDATE() WHERE compprofile_id = :compprofileId";
		SQLQuery query = session.createSQLQuery(sql);
		query.setInteger("status", compProfile.getStatus());
		query.setInteger("auditStatus", compProfile.getAuditStatus());
		query.setString("auditRemark", compProfile.getAuditRemark());
		query.setString("updUser", user.getId());
		query.setString("compprofileId", compProfile.getCompprofileId());
		query.executeUpdate();
		session.flush();

		writeDrugProfileLog(compProfile.getCompprofileId());
	}

}
